package com.ruoyi.oa.mapper;

import com.ruoyi.oa.domain.OaHumanClockCount;
import com.ruoyi.oa.domain.bo.OaHumanClockCountBo;
import com.ruoyi.oa.domain.bo.OaHumanSelectMonthBo;
import com.ruoyi.oa.domain.vo.OaHumanClockCountVo;
import com.ruoyi.common.core.mapper.BaseMapperPlus;
import com.ruoyi.oa.domain.OaHumanClockTotal;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 考勤记录Mapper接口
 *
 * @author ruoyi
 * @date 2023-07-19
 */
public interface OaHumanClockCountMapper extends BaseMapperPlus<OaHumanClockCountMapper, OaHumanClockCount, OaHumanClockCountVo> {

    /**
     * 根据用户查询当天打卡信息
     * @param userId
     * @return
     */
    @Select("SELECT * FROM oa_human_clock_count WHERE DATE(create_time) = DATE(NOW())\n" +
        " and user_id=#{userId}\n" +
        "limit 1")
    OaHumanClockCountVo selectTodayVoByUserId(@Param("userId") String userId);


    /**
     * 获取昨天用户的打卡记录
     * @param userId
     * @return
     */
    @Select("SELECT\n" +
        "\t* \n" +
        "FROM\n" +
        "\toa_human_clock_count \n" +
        "WHERE\n" +
        "\tDATE( create_time ) = DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AND user_id=#{userId} limit 1")
    OaHumanClockCountVo selectLastVoByUserId(@Param("userId") String userId);



    @Select("SELECT IFNULL((SELECT SUM(replace_count) FROM oa_human_clock_count " +
        "WHERE user_id=#{userId} and clock_date like CONCAT(CONCAT('%', #{dateString}), '%')), 0) ;")
    int getReplaceCountByIdAndMonth(@Param("userId") String userId,@Param("dateString") String dateString);

    /**
     * 根据打卡规则获取打卡记录
     */
    @Select("SELECT *\n" +
        "FROM  oa_human_clock_count\n" +
        "WHERE clock_date like CONCAT(CONCAT('%', #{dateString}), '%') AND rule_id=#{ruleId}")
    List<OaHumanClockCountVo> getTodayCountListByRuleId(@Param("ruleId") String ruleId,@Param("dateString")String dateString);


    /**
     * 通过月份以及用户id获取用户当月打卡条数
     */
    @Select("SELECT\n" +
        "\tCOUNT(*)\n" +
        "FROM\n" +
        "\toa_human_clock_count\n" +
        "\tWHERE clock_date LIKE CONCAT(CONCAT('%', #{dateString}), '%') and user_id=#{userId}  and clock_count!=0")
    Integer getUserClockCountByMonth(@Param("userId") String userId,@Param("dateString")String dateString);


    /**
     * 获取昨天打卡记录
     */
    @Select("SELECT *\n" +
        "FROM  oa_human_clock_count\n" +
        "WHERE DATE(create_time) = DATE_SUB( CURDATE(), INTERVAL 1 DAY )")
    List<OaHumanClockCountVo> getLastCountList();

    /**
     * 获取今日打卡记录
     * @return
     */
    @Select("SELECT *\n" +
        "FROM  oa_human_clock_count\n" +
        "WHERE DATE(create_time) = DATE(NOW())")
    List<OaHumanClockCountVo> getTodayCountList();


    /**
     * 统计所有用户查询信息
     * @return
     */
    List<OaHumanClockTotal> selectSumInfo(OaHumanClockCountBo bo);

    /**
     * 给app统计所有用户查询信息
     * @param bo
     * @return
     */
    List<OaHumanClockTotal> selectSumInfoForApp(OaHumanClockCountBo bo);

    /**
     * 通过条件查询打卡记录(只有部门)
     * @param bo
     * @return
     */
    List<OaHumanClockCountVo> selectGroupByUserId(OaHumanSelectMonthBo bo);


    @Select("SELECT *\n" +
        "FROM oa_human_clock_count\n" +
        "WHERE STR_TO_DATE(clock_date, '%Y/%m/%d') BETWEEN #{bo.startInfo} AND #{bo.endInfo} AND user_id=#{bo.userId}")
    List<OaHumanClockCountVo> selectByDateRange(@Param("bo") OaHumanSelectMonthBo bo);


    @Select("SELECT *\n" +
        "FROM oa_human_clock_count\n" +
        "WHERE clock_date like CONCAT(CONCAT('%', #{dateString}), '%') and user_id=#{userId}\n" +
        "limit 1")
    OaHumanClockCountVo selectLastByUserId(@Param("userId") String userId,@Param("dateString") String dateString);



    OaHumanClockTotal selectSumInfoByMonth(@Param("month") String month);


    OaHumanClockTotal selectSumInfoByMonthAndUserId(@Param("month") String month,@Param("userId") String userId);



    @Select("SELECT *\n" +
        "FROM oa_human_clock_count\n" +
        "WHERE clock_date like CONCAT(CONCAT('%', #{dateString}), '%')")
    List<OaHumanClockCountVo> selectCountByDate(@Param("dateString") String dateString);

    @Update("UPDATE oa_human_clock_count set end_time=NULL\n" +
        "WHERE id=#{id}")
    int cleanEndTime(@Param("id") String id);

    @Update("UPDATE oa_human_clock_count set start_time=NULL\n" +
        "WHERE id=#{id}")
    int cleanStartTime(@Param("id") String id);


    /**
     *
     * @param bo
     * @return
     */
    List<OaHumanClockCountVo> queryPerDaySum(OaHumanClockCountBo bo);



    @Select("SELECT COUNT(user_id) AS user_count\n" +
        "FROM (\n" +
        "    SELECT user_id, SUM(absent_count) AS total_absent_count\n" +
        "    FROM oa_human_clock_count\n" +
        "    WHERE clock_date LIKE CONCAT(CONCAT('%', #{month}), '%')\n" +
        "    GROUP BY user_id\n" +
        ") AS subquery\n" +
        "WHERE total_absent_count = 0;")
    Integer SelectNormalUserByMonth(String month);


    @Select("SELECT COUNT(user_id) AS user_count\n" +
        "FROM (\n" +
        "    SELECT user_id, SUM(absent_count) AS total_absent_count\n" +
        "    FROM oa_human_clock_count\n" +
        "    WHERE clock_date LIKE CONCAT(CONCAT('%', #{month}), '%')\n" +
        "    GROUP BY user_id\n" +
        ") AS subquery\n" +
        "WHERE total_absent_count != 0;")
    Integer SelectAbnormalUserByMonth(String month);


    @Select("SELECT\n" +
        "\tcount(*)\n" +
        "FROM\n" +
        "\toa_human_clock_count \n" +
        "WHERE\n" +
        "\t1 = 1 \n" +
        "\tAND clock_date LIKE CONCAT( CONCAT( '%', #{month}), '%' ) \n" +
        "\tAND clock_shift != '休息' \n" +
        "\tand is_outside=1\n" +
        "\tand user_id=#{userId}")
    Integer getOutSideCount(@Param("month") String month,@Param("userId") String userId);


    @Select("SELECT\n" +
        "\tCOUNT(*)\n" +
        "FROM\n" +
        "\toa_human_clock_count\n" +
        "\tWHERE clock_date LIKE CONCAT( CONCAT( '%', #{month}), '%' ) and user_id=#{userId} and adjust_name='正常(休息)' and clock_date not in\n" +
        "\t(Select holiday_date  from oa_holiday WHERE DATE_FORMAT(holiday_date, '%Y/%m')=#{month})")
    Integer getLeaveCount(@Param("month") String month,@Param("userId") String userId);


    @Select("SELECT *\n" +
        "FROM\n" +
        "\toa_human_clock_count b\n" +
        "\tWHERE DATE_FORMAT(clock_date, '%Y-%m') LIKE CONCAT(CONCAT('%', #{month}), '%') and b.user_id=#{userId} and b.is_outside=1 AND clock_date not in\n" +
        "\t(Select holiday_date  from oa_holiday WHERE DATE_FORMAT(holiday_date, '%Y-%m')=#{month})")
    List<OaHumanClockCountVo> getLeaveData(@Param("month") String month,@Param("userId") String userId);
}
